/**
	TO SAVE THE CUSTOMER WITH MINIMUM DETAILS. THIS PROC IS USED WHILE PORTING CUSTOMERS
	BEGIN
		DECLARE @CUSTOMER_ID INT 		
		EXECUTE SP_SAVE_CUSTOMER 'Sujan1', 'Sujan1', NULL, 'Customer', '', 0, @CUSTOMER_ID OUTPUT, 'Asia', 'shipmehtod', 'shipaccount'
		PRINT 'CUSTOMER ID=' + CONVERT(VARCHAR, @CUSTOMER_ID)
	END
*/

ALTER PROC SP_SAVE_CUSTOMER

@CUSTOMER_NAME    	VARCHAR( 65 ),
@CUSTOMER_NO      	VARCHAR( 50 ),
@DELIVERY_TERMS		VARCHAR( 50 ),
@C_TYPE_NAME            VARCHAR( 20 ),
@CUST_CONTACT_PERSON	VARCHAR( 50 ),
@IS_DISTRIBUTOR		INT, -- 0 = FALSE, 1 = TRUE
@CUSTOMER_ID         	INT OUT,
@REGION				VARCHAR(50),
@SHIPPING_METHOD	VARCHAR(50),
@SHIPPING_ACCOUNT	VARCHAR(50)

AS
BEGIN
	DECLARE @DB_C_TYPE_ID INT	
	DECLARE @DELIVERY_TERMS_ID INT
	DECLARE @DB_CUSTOMER_ID INT
	DECLARE @DUMMY_BILLING_ADDRESS_ID INT
	DECLARE @DUMMY_SHIPPING_ADDRESS_ID INT

	IF ( @CUSTOMER_NAME IS NULL OR LEN(LTRIM(RTRIM(@CUSTOMER_NAME))) = 0 )
	BEGIN
			UPDATE CUSTOMER SET C_NAME = 'CUSTOMER' WHERE C_NAME = 'CUSTOMER'
			RETURN
	END
	SET @DELIVERY_TERMS_ID = NULL	
	IF ( @DELIVERY_TERMS IS NOT NULL AND LEN(LTRIM(RTRIM(@DELIVERY_TERMS))) > 0 )
	 BEGIN		
		--GETTING DELIVERY TERMS ID
		SELECT @DELIVERY_TERMS_ID = DELIVERY_TERMS_ID FROM DELIVERY_TERMS WHERE DELIVERY_TERMS = LTRIM(RTRIM(@DELIVERY_TERMS))
		IF ( @DELIVERY_TERMS_ID IS NULL )
		 BEGIN
			INSERT INTO DELIVERY_TERMS(DELIVERY_TERMS) VALUES (LTRIM(RTRIM(@DELIVERY_TERMS)))
			SELECT @DELIVERY_TERMS_ID = @@IDENTITY
		 END
	 END
	
	SELECT @DB_C_TYPE_ID   = C_TYPE_ID FROM CUSTOMER_TYPES WHERE C_TYPE_NAME = @C_TYPE_NAME
	SELECT @DB_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME LIKE @CUSTOMER_NAME

	IF ( @@ROWCOUNT = 0 )
	 BEGIN		
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TAG_NAME, TIMESTAMP )
				VALUES ( '', '', '', '', '', '', '', '', '', '', '', '','', CURRENT_TIMESTAMP )
		SELECT @DUMMY_BILLING_ADDRESS_ID = @@IDENTITY
		
		INSERT INTO ADDRESS(ADDRESS_1, ADDRESS_2, ADDRESS_3, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, CELL_NUMBER, CONTACT_PERSON, TAG_NAME, TIMESTAMP )
				VALUES ( '', '', '', '', '', '', '', '', '', '', '', '', '', CURRENT_TIMESTAMP )
		SELECT @DUMMY_SHIPPING_ADDRESS_ID = @@IDENTITY
		
		IF ( @REGION IS NOT NULL AND LEN(LTRIM(RTRIM(@REGION))) != 0 )
		BEGIN
			IF((SELECT COUNT(*) FROM REGIONS_TABLE WHERE REGION_NAME=@REGION)=0)
			BEGIN
				INSERT INTO REGIONS_TABLE(REGION_NAME,DEACTIVATED) VALUES ( @REGION, 0 )
			END
		END
		
		INSERT INTO CUSTOMER ( C_NAME, C_NUMBER, C_CONTACT_PERSON, C_REGION, C_DEFAULT_BILLING_ADDRESS_ID, C_DEFAULT_SHIPPING_ADDRESS_ID, C_REP_OFFICE, C_CUSTOM_REQUIREMENTS, C_ECN_CHANGE_NOTIFICATION, C_DISABLED, C_Shipping_Method_1, C_Act_Number_1, C_TIMESTAMP, C_IS_DISTRIBUTOR, C_PAY_TERMS) 
			VALUES ( @CUSTOMER_NAME, @CUSTOMER_NO, @CUST_CONTACT_PERSON, @REGION, @DUMMY_BILLING_ADDRESS_ID, @DUMMY_SHIPPING_ADDRESS_ID,  '', 1, 1, 1,@SHIPPING_METHOD, @SHIPPING_ACCOUNT, CURRENT_TIMESTAMP, @IS_DISTRIBUTOR, '' ) 
		SELECT @DB_CUSTOMER_ID = @@IDENTITY

		IF ( ( SELECT COUNT (*) FROM CUSTOMER_TYPE_RELATIONSHIP WHERE CUSTOMER_ID = @DB_CUSTOMER_ID  AND C_TYPE_ID = @DB_C_TYPE_ID ) = 0 )		
		BEGIN
			INSERT INTO CUSTOMER_TYPE_RELATIONSHIP ( CUSTOMER_ID, C_TYPE_ID ) VALUES ( @DB_CUSTOMER_ID, @DB_C_TYPE_ID ) 
		END

		IF ( @CUST_CONTACT_PERSON IS NOT NULL AND LEN(LTRIM(RTRIM(@CUST_CONTACT_PERSON))) > 0 )
		 BEGIN
			INSERT INTO CUSTOMER_CONTACT_PERSONS_TABLE ( CUSTOMER_ID, CONTACT_PERSON, PHONE_NUMBER, CELL_NUMBER, FAX, EMAIL, Address, DEACTIVATED, SALES_ACK_MAIL, SHIP_ALERTS, PROFORMA_INVOICE_MAIL, PACKING_SLIP_MAIL, COMM_INVOICE_MAIL, QUOTE_ACK_MAIL )
					VALUES ( @DB_CUSTOMER_ID, @CUST_CONTACT_PERSON, '', '', '', '', '', 0, 1, 1, 1, 1, 1, 1 )
		 END

		IF ( (@SHIPPING_METHOD IS NOT NULL AND LEN(LTRIM(RTRIM(@SHIPPING_METHOD))) > 0) OR (@SHIPPING_ACCOUNT IS NOT NULL AND LEN(LTRIM(RTRIM(@SHIPPING_ACCOUNT))) > 0))
		BEGIN
			INSERT INTO Shipping_Methods_Table(SM_CUSTOMER_ID, SHIPPING_METHOD, SHIPPING_ACCOUNT, IS_DISABLED) VALUES(@DB_CUSTOMER_ID,ISNULL(@SHIPPING_METHOD,''),ISNULL(@SHIPPING_ACCOUNT,''),1)
		END
	 END
	/*ELSE --UPDATE
	 BEGIN		
	 END*/

	--UPDATING DELIVERY TERMS ID FOR THE CUSTOMER
	IF ( @DELIVERY_TERMS_ID IS NOT NULL )
	 BEGIN
		UPDATE CUSTOMER SET C_DELIVERY_TERMS_ID = @DELIVERY_TERMS_ID WHERE CUSTOMER_ID = @DB_CUSTOMER_ID
	 END
	SET @CUSTOMER_ID = @DB_CUSTOMER_ID
END

